package cn.edu.ecut.se2024.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.ecut.se2024.User.purchase;

public class service {
	String dbURL = "jdbc:sqlserver://localhost:1433;DatabaseName=Stock;encrypt=true;trustServerCertificate=true";
    String userName = "sa";
    String userPwd = "123456";
    Connection con;
	public service() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("驱动加载成功!");
			
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			con = DriverManager.getConnection(dbURL, userName, userPwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("数据库连接成功");
	}
	public  List<purchase> queryAllpurchase(){
		List<purchase> purchases=new ArrayList<>();
		PreparedStatement psmt=null;
		ResultSet rs=null;	
		String sql="select * from Purchase";
		try {
			psmt=con.prepareStatement(sql);
			rs=psmt.executeQuery();
			while(rs.next()) {
				int no =rs.getInt("number");
				String supplier=rs.getString("supplier");
				String id=rs.getString("pID");
				String name=rs.getString("pname");
				int number=rs.getInt("pquantity");
				int price=rs.getInt("pprice");
				purchase p=new purchase(no,supplier,id,name,number,price);
				purchases.add(p);
			}
		}
		 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return purchases;
	}
	public purchase querypurchaseBysno(int number) {
		PreparedStatement psmt=null;
		ResultSet rs=null;	
		String sql="select * from Purchase where number=?";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1, number);
			rs=psmt.executeQuery();
			if(rs.next()) {
				int no =rs.getInt("number");
				String supplier=rs.getString("supplier");
				String id=rs.getString("pID");
				String name=rs.getString("pname");
				int number1=rs.getInt("pquantity");
				int price=rs.getInt("pprice");
				purchase p=new purchase(no,supplier,id,name,number1,price);
				return p;
				
			}else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean isExist(int sno) {
		return (querypurchaseBysno(sno)==null)?false:true;
	}
	public boolean addpurchase(purchase p) {
		PreparedStatement psmt=null;
		String sql="insert into Purchase values(?,?,?,?,?,?)";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1,p.getNumber());
			psmt.setString(2,p.getSupplier());
			psmt.setString(3,p.getpID());
			psmt.setString(4,p.getPname());
			psmt.setInt(5,p.getPquantity());
			psmt.setInt(6,p.getPprice());
			int count=psmt.executeUpdate();
			if(count>0) return true;
			else return false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}
	public boolean deletepurchaseBysno(int sno) {
		PreparedStatement psmt=null;
		String sql="delete from Purchase where sno=?";
		try {
			psmt=con.prepareStatement(sql);
			psmt.setInt(1, sno);
			int count=psmt.executeUpdate();
			if(count>0) return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}
}